﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using ERPStudio;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Threading;
using System.Reflection;
using System.Globalization;
using System.IO;
using System.Text.RegularExpressions;

namespace Fares
{
    public partial class ImportFrm : FrmRezumat
    {
        ToolStripProgressBar pBar = null;
        private Dictionary<String, String> lstPartSchimb = new Dictionary<string, string>();
        private Boolean ParteneriProprii = true;
        public ImportFrm(ModuleClass mInfo)
            : base(mInfo)
        {
            InitializeComponent();
            workConnString = mInfo.workConnectionString;
        }

        private void ImportFrm_Load(object sender, EventArgs e)
        {
            aplicatieCB.Items.Add("Trafic");
            aplicatieCB.Items.Add("Evo XP");
            aplicatieCB.SelectedIndex = 0;
            LoadTabele();
            AddButton("Proceseaza[F9]", global::ERPStudio.Properties.Resources.gear, Proceseaza, ToolStripItemAlignment.Right, true);
            AddButton("Configurare", global::ERPStudio.Properties.Resources._001_08, IncarcaConfigurare, ToolStripItemAlignment.Right, true);
            AddButton("Anuleaza Import", global::ERPStudio.Properties.Resources._001_02, AnuleazaImport, ToolStripItemAlignment.Right, true);
            pBar = AddProgressBar("ProgressBar1", ToolStripItemAlignment.Left, 100);
            SqlCommand cmd2 = new SqlCommand("", DeschideConexiune(workConnString));
            cmd2.CommandText = "SELECT TOP 1 [Cont] FROM [Conturi] WHERE Cont <>'N/A' AND [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
            Object ret1v = cmd2.ExecuteScalar();
            if (ret1v == null)
            {
                //nu exista definit planul de conturi
                Object ret = GetSetupValue("BalconSetup", "TipUnitate", ModuleInfo.CodUnitate, cmd2.Connection);
                String valStr = String.Empty;
                if (ret == null || ret == DBNull.Value)
                {
                    while (ret == null)
                    {
                        MessageBox.Show("Modulul Balante Contabile nu a fost configurat. \nVa rugam sa-l configurati!",
                                "Eroare",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Hand);
                        #region Configurare Modul BalCon
                        FrmMainMdi frm = MdiParent as FrmMainMdi;
                        Assembly asm = frm.GetModule("erpforms");
                        if (asm == null)
                            return;
                        String numeClasa = "ERPForms.BalconSetupFrm";
                        Type mModule = asm.GetType(numeClasa);

                        if (mModule != null)
                        {
                            FrmCard newFrm = asm.CreateInstance(numeClasa, true, BindingFlags.Public | BindingFlags.Instance | BindingFlags.CreateInstance, null,
                               new object[] { ModuleInfo }, Thread.CurrentThread.CurrentCulture, new object[] { }) as FrmCard;

                            newFrm.ShowDialog();
                        }
                        #endregion
                        ///configurare modul-----------------------------------

                        ret = GetSetupValue("BalconSetup", "TipUnitate", ModuleInfo.CodUnitate, cmd2.Connection);
                    }
                }
                valStr = ret.ToString();
                PopuleazaConturi(valStr, cmd2.Connection);
            }

            ret1v = GetSetupValue("FaresSetup", "ParteneriProprii", ModuleInfo.CodUnitate, cmd2.Connection);
            if (ret1v != null && ret1v != DBNull.Value)
                ParteneriProprii = Convert.ToBoolean(ret1v);
  
            HandleSalvare += Proceseaza;
        }

        private void PopuleazaConturi(String TipUnitate, SqlConnection con)
        {
            #region Adaugare cont N/A
            ///verifica existenta contului radacina pentru unitatea curenta
            ///
            SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM [Conturi] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate + " AND [Cont]='N/A'",
                    con);
            Object ret = cmd.ExecuteScalar();
            if (ret != null && Convert.ToInt32(ret) == 0)
            {
                cmd.CommandText = "INSERT INTO [Conturi]([Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[Categorie]) VALUES ('N/A'," + ModuleInfo.CodUnitate.ToString() + ",'Principal','B',0,'N/A',1,0)";
                cmd.ExecuteNonQuery();
            }
            #endregion
            cmd.CommandText = "INSERT INTO [Conturi]([Cont],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[Categorie],[CodUnitate],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[RulajDebit] ,[RulajCredit])" +
                "SELECT  [Cont],[Denumire],[Tip],[Terminal],[ContParinte],0,[Categorie]," + ModuleInfo.CodUnitate.ToString() + ",0,0,0,0,0,0 FROM [PlanConturi] pl WHERE [TipUnitate]='" + TipUnitate + "' AND  [Preluare] = 1 " +
                " AND [ContParinte] in (SELECT [CONT] FROM [Conturi] where [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() +
                ") AND pl.[Cont] NOT IN (SELECT [CONT] FROM [Conturi] where [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() +
                ")";
            int caterec = 1;
            while (caterec != 0)
            {
                try
                {
                    caterec = cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

        private void IncarcaConfigurare(object sender, EventArgs e)
        { 
            openFileDialog1.Filter = "Fisiere DBF(*.dbf)|*.dbf|Toate fisierele (*.*)|*.*";
            openFileDialog1.FileName = "trafic.DBF";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                this.SuspendLayout();
                System.Data.OleDb.OleDbConnection dBaseConnection;
                dBaseConnection = new System.Data.OleDb.OleDbConnection();
                dBaseConnection.ConnectionString = "Provider=VFPOLEDB.1;Data Source=" + openFileDialog1.FileName;
                String drive = "C:";
                MatchCollection m = Regex.Matches(openFileDialog1.FileName,@"(?<valoare>\w:)");
                if(m.Count!=0)
                    drive = m[0].Value;

                String fName = openFileDialog1.SafeFileName.Replace(".DBF","");
                dBaseConnection.Open();
                dataSet1.Tables.Clear();
                OleDbDataAdapter dA = new OleDbDataAdapter("SELECT * FROM " + fName, dBaseConnection);
                dA.Fill(dataSet1, fName);
                dA.Dispose();

                tabeleDGV.Rows.Clear();
                tabeleDGV.Rows.Add(dataSet1.Tables[fName].Rows.Count);
                int row=0;
                foreach (DataRow drw in dataSet1.Tables[fName].Rows)
                {
                    String cale_prt = drw["path_prt"].ToString().Trim();
                    if (!cale_prt.Contains(":"))
                        cale_prt = drive + cale_prt;
                    String cale_far = drw["path_far"].ToString().Trim();
                    if (!cale_far.Contains(":"))
                        cale_far = drive + cale_far;

                    tabeleDGV[UnitateColumn.Index, row].Value = drw["titl_1"];
                    tabeleDGV[SursaColumn.Index, row].Value = drw["path_date"];
                    tabeleDGV[CaleParteneriColumn.Index, row].Value = cale_prt;
                    tabeleDGV[CaleFareColumn.Index, row].Value = cale_far;
                    
                    if (dBaseConnection.State == ConnectionState.Open)
                        dBaseConnection.Close();

                    dBaseConnection.ConnectionString = "Provider=VFPOLEDB.1;Data Source=" + cale_prt;
                    try
                    {
                        dBaseConnection.Open();
                        dA = new OleDbDataAdapter("SELECT * FROM PARTENER", dBaseConnection);
                        if (dataSet1.Tables["PARTENER"]!=null)
                            dataSet1.Tables["PARTENER"].Rows.Clear();
                        dA.Fill(dataSet1, "PARTENER");
                        tabeleDGV[ParteneriColumn.Index, row].Value = dataSet1.Tables["PARTENER"].Rows.Count.ToString();

                        dBaseConnection.Close();

                        dBaseConnection.ConnectionString = "Provider=VFPOLEDB.1;Data Source=" + cale_far;
                        dBaseConnection.Open();

                        dA.SelectCommand.CommandText = "SELECT * FROM REST";
                        if (dataSet1.Tables["REST"] != null)
                            dataSet1.Tables["REST"].Rows.Clear();
                        dA.Fill(dataSet1, "REST");
                        tabeleDGV[restanteColumn.Index, row].Value = dataSet1.Tables["REST"].Rows.Count.ToString();

                    }
                    catch(Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                        dA.Dispose();
                        row++;
                    }

                }
                dBaseConnection.Close();
                this.ResumeLayout();
            }
        }

        private void LoadTabele()
        { 
            switch(aplicatieCB.SelectedIndex)
            {
                case 0://trafic
                    break;
                case 1://evo
                    tabeleDGV.Rows.Add(3);
                    tabeleDGV[0, 0].Value = "Clienti";
                    tabeleDGV[0, 1].Value = "Agenti";
                    tabeleDGV[0, 2].Value = "Produse";
                    tabeleDGV[2, 0].Value = "...";
                    tabeleDGV[2, 1].Value = "...";
                    tabeleDGV[2, 2].Value = "...";
                    break;

            }
        }

        private void Proceseaza(object sender, EventArgs e)
        {
            if (tabeleDGV.SelectedCells.Count == 0)
            {
                MessageBox.Show("Va rugam selectati o unitate! Incarcati unitatile prin apasarea butonului 'Incarca configurare' ", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return;
            }

            Cursor.Current = Cursors.WaitCursor;
            SqlConnection wConn = DeschideConexiune(workConnString);
            
            String path1 = String.Empty;
            String fName = string.Empty;
            
            System.Data.OleDb.OleDbConnection dBaseConnection;
            dBaseConnection = new System.Data.OleDb.OleDbConnection();
            OleDbDataAdapter dA = null;
            SqlCommand comanda = new SqlCommand("", wConn);
            SqlTransaction tr = wConn.BeginTransaction();

            switch (aplicatieCB.SelectedIndex)
            {
                case 0://trafic
                    dBaseConnection.ConnectionString = "Provider=VFPOLEDB.1;Data Source=" +
                        tabeleDGV[CaleParteneriColumn.Index, tabeleDGV.SelectedCells[0].RowIndex].Value;
                    dA = new OleDbDataAdapter("SELECT * FROM PARTENER", dBaseConnection);
                    if (dataSet1.Tables["PARTENER"] != null)
                        dataSet1.Tables["PARTENER"].Rows.Clear();
                    dA.Fill(dataSet1, "PARTENER");
                    

                    comanda.Transaction = tr;
                    comanda.CommandText = "SET IDENTITY_INSERT PARTENERI ON";
                    comanda.ExecuteNonQuery();
                    pBar.Maximum = Convert.ToInt32(dataSet1.Tables["PARTENER"].Rows.Count);
                    pBar.Step = 1;

                    comanda.CommandText="INSERT INTO [ESRP].[dbo].[Parteneri]([IDPartener],[CUI],[Denumire],[CodUnitate],[ContBancar],[Cont])VALUES(0,999999,'Furnizor Necunoscut',0,'RO','401')";
                    try{comanda.ExecuteNonQuery();}
                    catch{}


                    foreach (DataRow drw in dataSet1.Tables["PARTENER"].Rows)
                    {
                        String cf = drw["Fisc"].ToString();
                        
                        String cp = drw["Cod_part"].ToString();
                        String denumire = drw["Partener"].ToString().Trim();
                        String banca = drw["Banca"].ToString().Trim();
                        String contbanca = drw["Cont"].ToString().Trim();
                        String judet = drw["Judet"].ToString().Trim();
                        String localitate = drw["Oras"].ToString().Trim();
                        String cont = drw["Contd_sin"].ToString().Trim();
                        if (drw["Contd_an1"].ToString().Trim() != string.Empty)
                            cont += "." + drw["Contd_an1"].ToString().Trim();
                        if (drw["Contd_an2"].ToString().Trim() != string.Empty)
                            cont += "." + drw["Contd_an2"].ToString().Trim();

                        String regcom = drw["Reg_comert"].ToString().Trim();
                        String[] rg = regcom.Split('/');
                        Int16 jrg = 0;
                        Int32 nrg = 0,arg=0;
                        if (rg.Length >= 3)
                        {
                            rg[0] = rg[0].Replace("J","");
                            try
                            {
                                jrg = Convert.ToInt16(rg[0]);
                                nrg = Convert.ToInt32(rg[1]);
                                arg = Convert.ToInt32(rg[2]);
                            }
                            catch
                            { 
                            
                            }
                        }
                        cf = cf.Replace("RO","");cf = cf.Replace("R","");
                        cf = cf.Replace("CNP", "");
                        cf = cf.Replace("F", ""); cf = cf.Replace("P", ""); 
                        cf = cf.Trim();
                        String AtributFiscal = String.Empty;
                        if(drw["Fisc"].ToString().Contains("R"))
                            AtributFiscal = "RO";

                        if (cf == String.Empty)
                        {
                            cf = DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();
                            MyTrace("Partener fara cod fiscal:" + denumire);
                        }
                        comanda.CommandText = "INSERT INTO Parteneri (IDPartener,CUI,Denumire,CodUnitate,TipPartener,IDBanca,ContBancar,IDJudet,IDLocalitate,AtributFiscal,Cont"
                            + (rg.Length >= 3 ? ",JudetRegComert,NrRegComert,AnRegComert" : "") + ") VALUES (" + cp + "," + cf + ",'" + denumire + "'," + ModuleInfo.CodUnitate.ToString() + ",'FC',Coalesce((SELECT MAX(IDBanca) FROM [Banci] WHERE Denumire LIKE '" + banca + "'),0),'" +
                           contbanca + "',Coalesce((SELECT MAX(IDJudet) FROM Judete WHERE Nume Like '"+judet+"'),0),Coalesce((SELECT MAX(IDLocalitate) FROM Localitati WHERE Nume LIKE '"+localitate +"'),0),'" +AtributFiscal+"'" +
                           (drw["Contd_sin"].ToString().Trim()=="0"?",'N/A'":",'" + cont + "'")  + (rg.Length >= 3 ? ","+jrg.ToString()+","+nrg.ToString()+","+arg.ToString() : "") +              
                           ")";
                        try
                        {
                            pBar.PerformStep();
                            comanda.ExecuteNonQuery();
                            
                        }
                        catch(Exception ex)
                        {
                            //MessageBox.Show(ex.ToString());
                            if (ex.Message.Contains("Conturi"))
                            {
                                SqlCommand cntcom = new SqlCommand("INSERT INTO [Conturi] (Cont,CodUnitate,Denumire,ContParinte) VALUES ('" +
                                    cont + "'," + ModuleInfo.CodUnitate.ToString() + ",'Fara denumire','" + drw["Contd_sin"].ToString().Trim() + "')",
                                    wConn);
                                cntcom.Transaction = tr;
                                cntcom.ExecuteNonQuery();
                            }
                            else
                                if (ex.Message.Contains("duplicate key"))
                                {
                                    ///pastram intr-o lista ce se va inlocui cu ce...
                                    ///
                                    SqlCommand cmdR = new SqlCommand("SELECT IDPartener FROM Parteneri WHERE CUI=" + cf + (ParteneriProprii ? " AND CodUnitate=" + ModuleInfo.CodUnitate : ""), wConn);
                                    cmdR.Transaction = tr;
                                    Object rrr = cmdR.ExecuteScalar();
                                    if (rrr != null && rrr != DBNull.Value)
                                    {
                                        String exID = rrr.ToString();
                                        lstPartSchimb.Add(cp, exID);
                                    }
                                    else
                                        MyTrace("Nu gasesc partenerul la schimb pentru: " + cp);
                                    continue;
                                }
                                else
                                {
                                    MyTrace(ex.Message);
                                    continue;
                                }
                            try
                            {
                                comanda.ExecuteNonQuery();
                            }
                            catch (Exception)
                            {
                                if (ex.Message.Contains("IX_Parteneri"))
                                {
                                    SqlCommand cntcom2 = new SqlCommand("UPDATE Parteneri SET Denumire = Denumire + '_OLD' WHERE IDPartener=" + cp + ModuleInfo.CodUnitate.ToString() + " AND CodUnitate=" + ModuleInfo.CodUnitate.ToString(),
                                        wConn);
                                    cntcom2.Transaction = tr;
                                    cntcom2.ExecuteNonQuery();
                                    comanda.ExecuteNonQuery();
                                }
                                else
                                    MessageBox.Show(ex.ToString());
                            }

                        }
                    }
                    comanda.CommandText = "SET IDENTITY_INSERT PARTENERI OFF";
                    comanda.ExecuteNonQuery();

                    tr.Commit();


                    dBaseConnection.ConnectionString = "Provider=VFPOLEDB.1;Data Source=" +
                        tabeleDGV[CaleFareColumn.Index, tabeleDGV.SelectedCells[0].RowIndex].Value;

                    dA.SelectCommand.CommandText = "SELECT * FROM REST";
                    if (dataSet1.Tables["REST"] != null)
                        dataSet1.Tables["REST"].Rows.Clear();
                    try
                    {
                        dA.Fill(dataSet1, "REST");
                    }
                    catch { }
                    dA.Dispose();

                    tr = comanda.Connection.BeginTransaction();
                    comanda.Transaction = tr;


                    #region insert restante
		            ///verificare existenta articol restanta import
                    #region verificare existenta articol import
		            comanda.CommandText = "SELECT IDArticol FROM [Articole] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND  Denumire = 'Restanta import fare'";
                    Object ret = comanda.ExecuteScalar();
                    Int32 idArticol = 0;
                    if (ret != null && ret != DBNull.Value)
                    {
                        idArticol = Convert.ToInt32(ret);
                    }
                    else
                    { 
                        comanda.CommandText = "INSERT INTO [Articole] ([Denumire],IDCategorie,CodUnitate,IDUnitateMasura,CodArticol) VALUES ('Restanta import fare'," +
                            "(SELECT MAX(IDCategorie) FROM TipCategoriiArticole),"+ModuleInfo.CodUnitate.ToString()+",(SELECT MAX(IDUnitateMasura) FROM UnitatiMasura),'IRF0')";
                        int cateret = comanda.ExecuteNonQuery();
                        comanda.CommandText = "SELECT IDArticol FROM [Articole] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND  Denumire = 'Restanta import fare'";
                        ret = comanda.ExecuteScalar();
                        idArticol = Convert.ToInt32(ret);
                    } 

	                #endregion

                    #region Verifica existenta partenerului necunoscut
                    ///verificare existenta articol restanta import
                    comanda.CommandText = "SELECT Count(*) FROM [Parteneri] WHERE IDPartener = 0";
                    ret = comanda.ExecuteScalar();
                    Int32 catePart = 0;
                    if (ret != null && ret != DBNull.Value)
                    {
                        catePart = Convert.ToInt32(ret);
                    }
                    if (catePart == 0)
                    {
                        comanda.CommandText = "SET IDENTITY_INSERT [parteneri] ON";
                        comanda.ExecuteNonQuery();
                        comanda.CommandText = "INSERT INTO [Parteneri](IDpartener,[CUI],[Denumire],[ContBancar],[Cont],[CodUnitate]) VALUES (0,0,'Partener necunoscut','RO','N/A'," + ModuleInfo.CodUnitate.ToString() + ")";
                        comanda.ExecuteNonQuery();
                        comanda.CommandText = "SET IDENTITY_INSERT [parteneri] OFF";
                        comanda.ExecuteNonQuery();
                    }

                    #endregion

                    /*comanda.CommandText = "SELECT IDUnitateMasura FROM [UnitatiMasura] WHERE  Denumire = 'Restanta import fare'";
                    ret = comanda.ExecuteScalar();*/

                    pBar.Maximum = Convert.ToInt32(dataSet1.Tables["REST"].Rows.Count);
                    pBar.Step = 1;
                    comanda.CommandText = "SET IDENTITY_INSERT Documente ON";
                    comanda.ExecuteNonQuery();
                    Boolean updateDoc = false;
                    foreach (DataRow drw in dataSet1.Tables["REST"].Rows)
                    {
                        updateDoc = false;
                        Decimal rest_plata = Convert.ToDecimal(drw["rest_plata"]);
                        if (rest_plata == Decimal.Zero)
                        {
                            pBar.PerformStep();
                            continue;
                        }
                        String iddoc = drw["Cod_fact"].ToString();
                        String nrdoc = drw["Doc"].ToString();
                        DateTime datadoc = Convert.ToDateTime(drw["Data"]);
                        DateTime datascad = Convert.ToDateTime(drw["Data_plata"]);
                        Int16 tip_tva = Convert.ToInt16(drw["tip_tva"]);
                        Int16 tip_doc = Convert.ToInt16(drw["tip_doc"]);
                        String cotaTVA = "0";
                        String idtipdoc = "1";
                        String tipop = "I";
                        if (tip_tva == 3)
                            cotaTVA = "19";
                        if (tip_doc == 1)
                        {
                            idtipdoc = "0";
                            tipop = "O";
                        }
                        else
                        {
                            if (faraVanzariCB.Checked)
                            {
                                pBar.PerformStep();
                                continue;
                            }

                        }
                        Decimal totalvaloare = Convert.ToDecimal(drw["Val_finala"]);
                        Decimal achitat = Convert.ToDecimal(drw["achitat"]);
                        Decimal totaltva = Convert.ToDecimal(drw["tvac"]);
                        if (tip_doc == 1)
                            totaltva = Convert.ToDecimal(drw["tvad"]);
                        String idpart = drw["Cod_part"].ToString();
                        Decimal idpartDec = Decimal.Zero;
                        if(!Decimal.TryParse(idpart,out idpartDec))
                        {
                            idpart ="0";
                        }
                        comanda.CommandText = "SET IDENTITY_INSERT Documente ON";
                        comanda.ExecuteNonQuery();
 


                        comanda.CommandText = "INSERT INTO Documente ([IDDocument],[NrDoc],[DataDoc],[IDPartener],[CodUnitate],[CotaTVA],"+
                            "[IDTipDocument],[TotalValoare],[TotalValoareTVA],[Total],[IDRegimFiscal],[UserId],[Observatii],[ValoareTotalaCuAdaos],[IDDepartament],[DataScadenta]) VALUES ("+
                            iddoc + ",'" + nrdoc + "','" +datadoc.Year.ToString()+"-" + datadoc.Month.ToString() + "-" + datadoc.Day.ToString() + "'," + idpart+ "," + ModuleInfo.CodUnitate.ToString() + ","+cotaTVA +","+
                            idtipdoc + "," + (totalvaloare).ToString("G", (CultureInfo.InvariantCulture)) + "," + totaltva.ToString("G", CultureInfo.InvariantCulture) + "," + (totalvaloare+totaltva).ToString("G", CultureInfo.InvariantCulture) + ",4,'" + ModuleInfo.UserID.ToString() + "','',0,null,'"+
                            datascad.Year.ToString() + "-" + datascad.Month.ToString() + "-" + datascad.Day.ToString() + "')";
                        try
                        { 
                            pBar.PerformStep();
                            //MyTrace(comanda.CommandText);
                            comanda.ExecuteNonQuery();
                            comanda.CommandText = "INSERT INTO [fares_DocumenteImportate]([Cheie],[Tabela],[Operatie],[CodUnitate]) VALUES (" + iddoc + ",'Documente','I',"+ModuleInfo.CodUnitate.ToString()+")";
                            comanda.ExecuteNonQuery();
                        }
                        catch (Exception exp)
                        {
                            if (exp.Message.Contains("FK_Documente_Parteneri") || exp.Message.Contains("PK_Documente"))
                            {
                                //MyTrace(exp.Message);
                                Int32 nrRand = 0;
                                SqlCommand cntcom = new SqlCommand("",wConn);
                                SqlCommand cntcomi = new SqlCommand("", wConn);
                                cntcom.Transaction = tr;
                                while (nrRand==0)
                                {
                                    if (exp.Message.Contains("FK_Documente_Parteneri"))
                                    {
                                        //daca nu gaseste partenerul il cauta in lista de schimb
                                        String nouID = "0";
                                        if (lstPartSchimb.TryGetValue(idpart, out nouID))
                                            MyTrace("Schimbat Partener:" + idpart + "->" + nouID);
                                        idpart = nouID;
                                    }
                                    if (exp.Message.Contains("Dublare_Documente_Furnizor_Numar_Data"))
                                    {
                                        nrdoc += "0";
                                    }
                                    if (exp.Message.Contains("PK_Documente"))
                                    {
                                        //iddoc = DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();
                                        //modificam documentul existent
                                        cntcom.CommandText = "UPDATE Documente SET [NrDoc]='" + nrdoc + "',[DataDoc]='" + datadoc.Year.ToString() + "-" + datadoc.Month.ToString() + "-" + datadoc.Day.ToString() +
                                            "',[IDPartener]=" + idpart + ",[CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + ",[CotaTVA]=" + cotaTVA + "," +
                                            "[IDTipDocument]=" + idtipdoc + ",[TotalValoare]=" + (totalvaloare).ToString("G", (CultureInfo.InvariantCulture)) + ",[TotalValoareTVA]=" + totaltva.ToString("G", CultureInfo.InvariantCulture) + ",[Total]="
                                        + (totalvaloare+totaltva).ToString("G", CultureInfo.InvariantCulture) + ",[IDRegimFiscal]=4,[UserId]='" + ModuleInfo.UserID.ToString() + "',[Observatii]='modificat de import',[ValoareTotalaCuAdaos]=0,[IDDepartament]=null,[DataScadenta]='" +
                                        datascad.Year.ToString() + "-" + datascad.Month.ToString() + "-" + datascad.Day.ToString() + "' WHERE [IDDocument]=" + iddoc;
                                        updateDoc = true;
                                        cntcomi.CommandText = "UPDATE [fares_DocumenteImportate] SET [Operatie]='M' WHERE [Cheie] = " + iddoc + "  AND [Tabela]='Documente' AND [CodUnitate] =" +  ModuleInfo.CodUnitate.ToString();
                                    }
                                    else
                                    {
                                        cntcom.CommandText = "INSERT INTO Documente ([IDDocument],[NrDoc],[DataDoc],[IDPartener],[CodUnitate],[CotaTVA]," +
                                        "[IDTipDocument],[TotalValoare],[TotalValoareTVA],[Total],[IDRegimFiscal],[UserId],[Observatii],[ValoareTotalaCuAdaos],[IDDepartament],[DataScadenta]) VALUES (" +
                                        iddoc + ",'" + nrdoc + "','" + datadoc.Year.ToString() + "-" + datadoc.Month.ToString() + "-" + datadoc.Day.ToString() + "'," + idpart + "," + ModuleInfo.CodUnitate.ToString() + "," + cotaTVA + "," +
                                        idtipdoc + "," + (totalvaloare).ToString("G", (CultureInfo.InvariantCulture)) + "," + totaltva.ToString("G", CultureInfo.InvariantCulture) + "," + (totalvaloare+totaltva).ToString("G", CultureInfo.InvariantCulture) + ",4,'" + ModuleInfo.UserID.ToString() + "','',0,null,'" +
                                        datascad.Year.ToString() + "-" + datascad.Month.ToString() + "-" + datascad.Day.ToString() + "')";
                                        cntcomi.CommandText = "INSERT INTO [fares_DocumenteImportate]([Cheie],[Tabela],[Operatie],[CodUnitate]) VALUES (" + iddoc + ",'Documente','I'," + ModuleInfo.CodUnitate.ToString() + ")";
                                    }
                                    try
                                    {
                                        //MyTrace(cntcom.CommandText);
                                        nrRand = cntcom.ExecuteNonQuery();

                                        try
                                        {
                                            cntcomi.Transaction = tr;
                                            int nrc = cntcomi.ExecuteNonQuery();
                                            if (nrc == 0 && updateDoc)
                                            {
                                                cntcomi.CommandText = "INSERT INTO [fares_DocumenteImportate]([Cheie],[Tabela],[Operatie],[CodUnitate]) VALUES (" + iddoc + ",'Documente','I'," + ModuleInfo.CodUnitate.ToString() + ")";
                                                cntcomi.ExecuteNonQuery();
                                            }
                                        }
                                        catch(Exception Ex)
                                        {
                                            MyTrace(Ex.Message);
                                        }


                                    }
                                    catch (Exception ex)
                                    {
                                        exp = ex;
                                        MyTrace(exp.Message);
                                        if (!exp.Message.Contains("FK_Documente_Parteneri") && !exp.Message.Contains("PK_Documente") && !exp.Message.Contains("Dublare_Documente_Furnizor_Numar_Data"))
                                        {
                                            MessageBox.Show(exp.Message);
                                            MyTrace("Eroare:" + exp.Message);
                                            MyTrace(cntcom.CommandText);
                                            break;
                                        }
                                    }
                                }
                            }
                            else
                                {
                                    MessageBox.Show(exp.Message);
                                    MyTrace("Eroare:" + exp.Message);
                                    MyTrace(comanda.CommandText);
                                    tr.Rollback();
                                    return;
                                }
                        }
                        try
                        {
                            //detaliu nu se salveaza in informatii
                            String comandaUpdate = "UPDATE [DocumenteDetaliu] SET [IDArticol]="+ idArticol.ToString() +",[Cantitate]=1,[ValoareUnitara]="+ (totalvaloare).ToString("G", (CultureInfo.InvariantCulture)) +
                                    ",[ValoareTVA]="+ totaltva.ToString("G", CultureInfo.InvariantCulture) +",[ValoareTotala]="+(totalvaloare+totaltva).ToString("G", CultureInfo.InvariantCulture) +",[CodUnitate]=" +
                                     ModuleInfo.CodUnitate.ToString() + ",[IDUnitateMasura]=0,[IDRegimFiscal]=4,[CotaTVA]="+ cotaTVA +",[Observatii]='modificat la preluare date',[ValoareTotalaCuAdaos]=0 WHERE [IDDocument]=" 
                                     +iddoc;
                            String cmdUpdateInfo = String.Empty;
                            //"UPDATE [fares_DocumenteImportate] SET [Operatie]='M' WHERE [Cheie] = " + iddoc + "  AND [Tabela]='DocumenteDetaliu' AND ,[CodUnitate] =" + ModuleInfo.CodUnitate.ToString();
                            String comandaInsert = "INSERT INTO [DocumenteDetaliu]([IDDocument],[IDArticol],[Cantitate],[ValoareUnitara],[ValoareTVA],[ValoareTotala],[CodUnitate]," +
                                "[IDUnitateMasura],[IDRegimFiscal],[CotaTVA],[Observatii],[ValoareTotalaCuAdaos]) VALUES (" +
                                 iddoc + "," + idArticol.ToString() + ",1," + (totalvaloare).ToString("G", (CultureInfo.InvariantCulture)) + "," + totaltva.ToString("G", CultureInfo.InvariantCulture) + "," +
                                 (totalvaloare+totaltva).ToString("G", CultureInfo.InvariantCulture) + "," + ModuleInfo.CodUnitate.ToString() + "," +
                                 "0,4," + cotaTVA + ",'',0)";
                            String cmdInsertInfo = String.Empty;
                            //"INSERT INTO [fares_DocumenteImportate]([Cheie],[Tabela],[Operatie],[CodUnitate]) VALUES (" + iddoc + ",'DocumenteDetaliu','I'," + ModuleInfo.CodUnitate.ToString() + ")";
                            
                            if (updateDoc)
                            {
                                comanda.CommandText = comandaUpdate;
                            }
                            else
                                comanda.CommandText = comandaInsert;
                            //MyTrace(comanda.CommandText);
                            int cateins = comanda.ExecuteNonQuery();
                            if (cateins == 0 && updateDoc)
                            {
                                //nu s-a odificat nici un document deci se adauga
                                comanda.CommandText = comandaInsert;
                                comanda.ExecuteNonQuery();
                            }


                            if (rest_plata != Decimal.Zero && (Decimal.Round(totalvaloare + totaltva - rest_plata,2) !=Decimal.Zero))
                            {
                                comanda.CommandText = "SET IDENTITY_INSERT Documente OFF";
                                comanda.ExecuteNonQuery();
                                comanda.CommandText = "SET IDENTITY_INSERT DocumentePlata ON";
                                comanda.ExecuteNonQuery();
                                String tipoppla = "I";
                                if (tipop=="I")
                                    tipoppla = "O";

                                comandaInsert = "INSERT INTO [DocumentePlata]([IDDocumentPlata],[Numar],[Data],[IDTipPlata],[ValoareTotala] ," +
                                    "[IDPartener],[Reprezentand],[TipOperatiune],[CodUnitate]) VALUES (" +
                                    iddoc + ",'" + nrdoc + "P','" + datadoc.Year.ToString() + "-" + datadoc.Month.ToString() + "-" + datadoc.Day.ToString() + "',1," + (Decimal.Round(totalvaloare+totaltva-rest_plata,2)).ToString("G", CultureInfo.InvariantCulture) + "," +
                                    idpart + ",'achitat restanta fare','" + tipoppla + "'," + ModuleInfo.CodUnitate.ToString() + ")";
                                cmdUpdateInfo = "UPDATE [fares_DocumenteImportate] SET [Operatie]='M' WHERE [Cheie] = " + iddoc + "  AND [Tabela]='DocumentePlata' AND [CodUnitate] =" + ModuleInfo.CodUnitate.ToString();

                                comandaUpdate = "UPDATE [DocumentePlata] SET [Numar]='"+ nrdoc +"',[Data]='"+ datadoc.Year.ToString() + "-" + datadoc.Month.ToString() + "-" + datadoc.Day.ToString() +"',[IDTipPlata]=1"+
                                ",[ValoareTotala]="+ (Decimal.Round(totalvaloare+totaltva - rest_plata,2)).ToString("G", CultureInfo.InvariantCulture) +",[IDPartener]="+
                                    idpart +",[Reprezentand]='achitat restanta fare - modificat',[TipOperatiune]='"+ tipoppla +"',[CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " WHERE [IDDocumentPlata]="+
                                    iddoc;
                                cmdInsertInfo = "INSERT INTO [fares_DocumenteImportate]([Cheie],[Tabela],[Operatie],[CodUnitate]) VALUES (" + iddoc + ",'DocumentePlata','I'," + ModuleInfo.CodUnitate.ToString() + ")";
                                if (updateDoc)
                                {
                                    comanda.CommandText = comandaUpdate;
                                }
                                else
                                    comanda.CommandText = comandaInsert;
                                //MyTrace(comanda.CommandText);
                                cateins = comanda.ExecuteNonQuery();
                                if (cateins == 0 && updateDoc)
                                {
                                    //nu s-a odificat nici un document deci se adauga
                                    comanda.CommandText = comandaInsert;
                                    comanda.ExecuteNonQuery();
                                    comanda.CommandText = cmdInsertInfo;
                                }
                                else
                                {
                                    comanda.CommandText = cmdUpdateInfo;
                                }

                                try
                                {
                                    int nrc = comanda.ExecuteNonQuery();
                                    if (nrc == 0 && updateDoc)
                                    {
                                        comanda.CommandText = cmdInsertInfo;
                                        comanda.ExecuteNonQuery();
                                    }
                                }
                                catch (Exception Ex)
                                {
                                    MyTrace(Ex.Message);
                                }
                                

                                comandaInsert = "INSERT INTO [DocumentePlataDetaliu]([IDDocument],[IDDocumentPlata],[Valoare]) VALUES (" +
                                    iddoc + "," + iddoc + "," + (totalvaloare+totaltva - rest_plata).ToString("G", CultureInfo.InvariantCulture) + ")";

                                comandaUpdate = "UPDATE [DocumentePlataDetaliu] SET [Valoare]="
                                      + (totalvaloare+totaltva - rest_plata).ToString("G", CultureInfo.InvariantCulture) + " WHERE [IDDocument]=" +
                                    iddoc + " AND [IDDocumentPlata]="+ iddoc ;

                                //MyTrace(comanda.CommandText);
                                if (updateDoc)
                                {
                                    comanda.CommandText = comandaUpdate;
                                }
                                else
                                    comanda.CommandText = comandaInsert;
                                //MyTrace(comanda.CommandText);
                                cateins = comanda.ExecuteNonQuery();
                                if (cateins == 0 && updateDoc)
                                {
                                    //nu s-a odificat nici un document deci se adauga
                                    comanda.CommandText = comandaInsert;
                                    comanda.ExecuteNonQuery();
                                }

                               

                                comanda.CommandText = "SET IDENTITY_INSERT DocumentePlata OFF";
                                comanda.ExecuteNonQuery();
                                comanda.CommandText = "SET IDENTITY_INSERT Documente ON";
                                comanda.ExecuteNonQuery();
                            }
                            else
                            {
                                comanda.CommandText = "DELETE FROM [DocumentePlataDetaliu] WHERE [IDDocumentPlata]=" + iddoc;
                                comanda.ExecuteNonQuery();
                                comanda.CommandText = "DELETE FROM [DocumentePlata] WHERE [IDDocumentPlata]=" + iddoc;
                                comanda.ExecuteNonQuery();
                                comanda.CommandText = "DELETE FROM [fares_DocumenteImportate] WHERE [Cheie]=" + iddoc + " AND [Tabela] = 'DocumentePlata' AND [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                                comanda.ExecuteNonQuery();
                            }
                            updateDoc = false;
                        }
                        




                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                            tr.Rollback();
                            return;
                        }
                    }
 
                    comanda.CommandText = "SET IDENTITY_INSERT Documente OFF";
                    comanda.ExecuteNonQuery();
	                #endregion


                    break;

                case 1://evo

                    foreach (DataGridViewRow dr in tabeleDGV.Rows)
                    {
                        if (dr.Cells[1] != null && dr.Cells[1].Value != null && dr.Cells[1].Value.ToString() != String.Empty)
                        {
                            path1 = dr.Cells[1].Value.ToString().Replace(dr.Cells[1].Tag.ToString(), "");
                            fName = dr.Cells[1].Tag.ToString().ToUpper().Replace(".DBF", "");
                            dBaseConnection.ConnectionString = "Provider=VFPOLEDB.1;Data Source=" + path1;
                            dBaseConnection.Open();
                            dA = new OleDbDataAdapter("SELECT * FROM " + fName, dBaseConnection);
                            dA.Fill(dataSet1, fName);
                            dA.Dispose();

                            foreach (DataRow drw in dataSet1.Tables[fName].Rows)
                            {
                                if (fName == "CLIENTI")
                                {
                                    /*clientiBindingSource.AddNew();
                                    DataRowView drwCli = clientiBindingSource.Current as DataRowView;
                                    drwCli.Row["Denumire"] = drw["DenCli"];
                                    drwCli.Row["CIF"] = drw["CodFisc"].ToString().Substring(0,10);
                                    drwCli.Row["Judet"] = drw["Judet"];
                                    drwCli.Row["Localitate"] = drw["Oras"];
                                    drwCli.Row["Strada"] = drw["Strada"];
                                    drwCli.Row["Numar"] = (drw["Nr"].ToString()==String.Empty?drw["Bloc"] + "/" + drw["Ap"]:drw["Nr"]) ;
                                    drwCli.Row["Banca"] = drw["Banca"];
                                    drwCli.Row["Cont"] = drw["ContBanca"];
                                    drwCli.Row["Adresa"] = drw["Adresa"];
                                    drwCli.Row["Sector"] = 0;
                                    if (drw["Sector"].ToString().Trim() != String.Empty)
                                    {
                                        drwCli.Row["Sector"] = Convert.ToInt16(drw["Sector"].ToString());
                                    }
                                    drwCli.Row["CodUnitate"] = ModuleInfo.CodUnitate;
                                    clientiBindingSource.EndEdit();*/
                                }
                                if (fName == "AGENTI")
                                {
                                    /*agentiBindingSource.AddNew();
                                    DataRowView drwCli = agentiBindingSource.Current as DataRowView;
                                    drwCli.Row["NumePrenume"] = drw["Agent"];
                                    drwCli.Row["CodUnitate"] = ModuleInfo.CodUnitate;
                                    agentiBindingSource.EndEdit();*/
                                }
                                if (fName == "PRODUSE")
                                {
                                    /*produseBindingSource.AddNew();
                                    DataRowView drwCli = produseBindingSource.Current as DataRowView;
                                    drwCli.Row["Denumire"] = drw["Denp"].ToString().Substring(0,50);
                                    drwCli.Row["Cod"] = drw["Codp"].ToString().Substring(0,10);
                                    //drwCli.Row["CodUnitate"] = ModuleInfo.CodUnitate;
                                    produseBindingSource.EndEdit();*/
                                }

                            }
                            /*if (val)
                            {
                                SqlCommand comm = new SqlCommand("DELETE FROM " + fName, wConn);
                                comm.ExecuteNonQuery();
                            }*/

                            if (fName == "CLIENTI")
                            {
                                /* clientiTableAdapter.Connection = wConn;
                                 clientiTableAdapter.Update(eSRPDataSet.Clienti);   */
                            }
                            if (fName == "AGENTI")
                            {
                                /*agentiTableAdapter.Connection = wConn;
                                agentiTableAdapter.Update(eSRPDataSet.Agenti);*/
                            }
                            if (fName == "PRODUSE")
                            {
                                /*produseTableAdapter.Connection = wConn;
                                produseTableAdapter.Update(eSRPDataSet.Produse);*/
                            }

                            dBaseConnection.Close();

                        }
                    }
                    break;
            }
            
            dBaseConnection.Dispose();
            tr.Commit();
            InchideConexiune(wConn);

            Cursor.Current = Cursors.Default;
            SetStatus("Importul datelor s-a incheiat cu succes!!");
        }

        private void tabeleDGV_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 2)
            { 
                //clic pe butonul de selectie cale...
                openFileDialog1.Filter = "Fisiere DBF(*.dbf)|*.dbf|Toate fisierele (*.*)|*.*";
                openFileDialog1.FileName = tabeleDGV[0, e.RowIndex].Value.ToString() + ".DBF";
                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    tabeleDGV[1, e.RowIndex].Value = openFileDialog1.FileName;
                    tabeleDGV[1, e.RowIndex].Tag = openFileDialog1.SafeFileName;
                }
            }
        }

        private void AnuleazaImport(object sender, EventArgs e)
        {
            Type mModule = null;
            FrmRezumat frm = RuleazaFrm("erpforms", "ERPForms.ConfirmareOperatieFrm", new object[] { },out mModule) as FrmRezumat;
            int catefact = 0, cateplati = 0;
            if (frm!=null && frm.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            { 
                //stergem importurile anterioare
                SqlConnection con = DeschideConexiune(workConnString);
                SqlTransaction tr = con.BeginTransaction();
                SqlCommand cmd = new SqlCommand("DELETE [DocumentePlataDetaliu] FROM [fares_DocumenteImportate] d WHERE d.[CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND CAST([DocumentePlataDetaliu].IDDocumentPlata AS bigint)=d.Cheie" +
                " AND d.Tabela = 'DocumentePlata' ",con);
                cmd.Transaction = tr;
                try
                {
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "DELETE [DocumentePlata] FROM [fares_DocumenteImportate]  d WHERE d.[CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND CAST([DocumentePlata].IDDocumentPlata AS bigint)=d.Cheie" +
                    " AND d.Tabela = 'DocumentePlata' ";
                    cateplati = cmd.ExecuteNonQuery();


                    cmd.CommandText = "DELETE [DocumenteDetaliu] FROM [fares_DocumenteImportate]  d WHERE d.[CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND CAST([DocumenteDetaliu].IDDocument AS bigint)=d.Cheie" +
                    " AND d.Tabela = 'Documente' ";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "DELETE [Documente] FROM [fares_DocumenteImportate]  d WHERE d.[CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND CAST([Documente].IDDocument AS bigint)=d.Cheie" +
                    " AND d.Tabela = 'Documente' ";
                    catefact = cmd.ExecuteNonQuery();

                    cmd.CommandText = "DELETE FROM [fares_DocumenteImportate]  WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND Tabela in ('Documente','DocumentePlata')";
                    cmd.ExecuteScalar();

                    tr.Commit();
                }
                catch(Exception Ex)
                {
                    MyTrace("Stergere importuri:" + Ex.Message);
                    tr.Rollback();
                }
                finally
                {
                    InchideConexiune(con);
                }
            }
            SetStatus("Importul datelor a fost anulat!!(" + catefact.ToString() + "," + cateplati.ToString() +")");
        }
    }
}
